# -*- coding: utf-8 -*-
"""
__title__ = ''
__author__ = 'Yang'
__mtime__ = '2018/12/19 20:32'
# code is far away from bugs with the god animal protecting
    I love animals. They taste delicious.
              ┏┓      ┏┓
            ┏┛┻━━━┛┻┓
            ┃      ☃      ┃
            ┃  ┳┛  ┗┳  ┃
            ┃      ┻      ┃
            ┗━┓      ┏━┛
                ┃      ┗━━━┓
                ┃  神兽保佑    ┣┓
                ┃　永无BUG！   ┏┛
                ┗┓┓┏━┳┓┏┛
                  ┃┫┫  ┃┫┫
                  ┗┻┛  ┗┻┛
"""
import numpy as np
import pandas as pd
import pymysql
import time
import re
from sqlalchemy import create_engine

"""
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| book_isbn    | varchar(20)  | YES  |     | NULL    |                |
| book_name    | varchar(200) | YES  |     | NULL    |                |
| book_price   | double       | YES  |     | NULL    |                |
| book_count   | bigint(20)   | YES  |     | NULL    |                |
| book_author  | varchar(100) | YES  |     | NULL    |                |
| book_publish | varchar(50)  | YES  |     | NULL    |                |
| book_img     | varchar(20)  | YES  |     | NULL    |                |
| book_type    | varchar(5)   | YES  | MUL | NULL    |                |
| book_label   | varchar(10)  | YES  |     | NULL    |                |
| book_intro   | text         | YES  |     | NULL    |                |
| book_outline | text         | YES  |     | NULL    |                |
"""
time_start = time.time()
conn = create_engine('mysql+pymysql://root:123@localhost:3306/student?charset=utf8')
pd.set_option('display.max_rows', 5000)
pd.set_option('display.max_columns', 5000)
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='123', db='student')
cursor = db.cursor()
cursor.execute('select * from tb_books')
data = cursor.fetchall()

df = pd.DataFrame(list(data),
                  columns='id,book_isbn,book_name,book_price,book_count,book_author,book_publish,book_img,book_type,book_label,book_intro,book_outline'.split(
                      ','))
print(df.shape)
# df = df.loc[:,
#      ['book_isbn', 'book_name', 'book_author', 'book_publish', 'book_price', 'book_count', 'book_type', 'book_label',
#       'book_intro', 'book_img']]
#
# df = df.groupby(by='book_type').head(100)
# print(df.shape)
# df.index = range(len(df))
df.to_excel('df.xls', encoding='utf-8')
